package dbhelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import entities.Bid;

public class Bids {
	private static final String tableName = " ebaydb.bids ";
	private static final String newBidQuerySQL = "INSERT INTO" + tableName
			+ "(item_id,user_id,money,bid_time) VALUES (?,?,?,?);";
	private static final String biddedItemsQuery = 
			"SELECT * FROM" + tableName + "," +
	Items.tableName + "WHERE bids.item_id = items.id AND bids.user_id = ?   ORDER BY BID_TIME DESC LIMIT ?,?";
	private static final String biddedItemsStartFromTimeQuery = 
			biddedItemsQuery.replace("   "," AND bids.bid_time > ? ");
	private static final int BIDS_PER_PAGE = 20;
	
	public static boolean placeNewBid(Bid bid) {
		PreparedStatement newBidPS = null;
		Connection connection = null;
		try {
			connection = MyDBConnectionPool.getConnection();
			newBidPS = connection.prepareStatement(
					newBidQuerySQL);
			newBidPS.setLong(1, bid.getItemID());
			newBidPS.setLong(2, bid.getUserID());
			newBidPS.setDouble(3, bid.getMoney());
			newBidPS.setTimestamp(4, new Timestamp(Calendar.getInstance().getTimeInMillis()));
			return newBidPS.executeUpdate() == 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (newBidPS != null)
					newBidPS.close();
				if(connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}
	

	
	public static List<Bid> getBids(long userID,int pageNumber,Timestamp startFrom){
		List<Bid> result = new ArrayList<Bid>();
		 PreparedStatement getBidsPS = null;
		 Connection connection = null;
		 String query = (startFrom == null) ? biddedItemsQuery : biddedItemsStartFromTimeQuery;
		try {
			connection = MyDBConnectionPool.getConnection();
			getBidsPS = connection.prepareStatement(query);
			int injectedParametersCounter = 1;
			getBidsPS.setLong(injectedParametersCounter++, userID);
			if(startFrom != null) getBidsPS.setTimestamp(injectedParametersCounter++, startFrom);
			int offset = BIDS_PER_PAGE * pageNumber;
			getBidsPS.setInt(injectedParametersCounter++, offset);
			getBidsPS.setInt(injectedParametersCounter++, BIDS_PER_PAGE + offset);
			populateBids(
					result,getBidsPS.executeQuery(),false
					);
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(getBidsPS != null)
					getBidsPS.close();
				if(connection != null)
					connection.close();
			} catch (SQLException e) {
			e.printStackTrace();
			}
		}
		return null;
	}



	private static void populateBids(List<Bid> result, ResultSet rs,
			boolean b) {

		try {
			while(rs.next()){
				Bid bid = new Bid();
				bid.setItemName(rs.getString("item_name"));
				bid.setItemID(rs.getLong("item_id"));
				bid.setMoney(rs.getDouble("money"));
				bid.setTime(rs.getTimestamp("bid_time"));
				Timestamp sellTime = rs.getTimestamp("sell_time");
				bid.setSellTime(sellTime);
				boolean sold = sellTime.before(
						new Timestamp(new java.util.Date().getTime())
						);
				bid.setIfOver(sold);
				bid.setIfWon(sold ? (rs.getLong("winner_user_id") == rs.getLong("user_id")) : false);
				result.add(bid);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try{
				if(rs != null)
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
	}
	
}
